package com.yu.dv.strategy.metadata;

import com.yu.dv.entity.ColumnInfo;
import com.yu.dv.entity.IndexInfo;
import com.yu.dv.entity.PrimaryKeyInfo;
import com.yu.dv.entity.TableInfo;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * 元数据获取模版抽象类
 * 定义标准的元数据获取流程，具体步骤由子类实现
 */
@Slf4j
public abstract class MetadataTemplate implements MetadataStrategy {

    /**
     * 模版方法：获取所有表的元数据信息（标准流程）
     */
    @Override
    public Map<String, Object> getAllTablesMetadata(Connection connection) throws Exception {
        Map<String, Object> result = new HashMap<>();
        DatabaseMetaData metaData = connection.getMetaData();

        try {
            // 1. 获取数据库基本信息
            result.put("databaseInfo", getDatabaseInfo(metaData));

            // 2. 获取catalog和schema（由子类实现）
            String catalog = getCatalog(connection);
            String schema = getSchema(connection);

            // 3. 获取表类型（由子类实现）
            String[] tableTypes = getTableTypes();

            // 4. 获取表列表（标准方法）
            List<TableInfo> tables = getTablesStandard(metaData, catalog, schema, tableTypes);

            // 5. 如果标准方法失败，使用备用方案（由子类实现）
            if (tables.isEmpty()) {
                tables = getTablesWithFallback(connection, catalog, schema);
            }

            // 6. 丰富表信息
            enrichTablesInfo(metaData, catalog, schema, tables);

            result.put("tables", tables);
            result.put("tableCount", tables.size());
            result.put("success", true);
            result.put("strategy", this.getClass().getSimpleName());

        } catch (Exception e) {
            log.error("获取数据库元数据失败", e);
            result.put("error", e.getMessage());
            result.put("success", false);
            throw e;
        }

        return result;
    }

    /**
     * 获取数据库基本信息
     */
    protected Map<String, Object> getDatabaseInfo(DatabaseMetaData metaData) throws SQLException {
        Map<String, Object> info = new HashMap<>();
        info.put("databaseProductName", metaData.getDatabaseProductName());
        info.put("databaseProductVersion", metaData.getDatabaseProductVersion());
        info.put("url", metaData.getURL());
        info.put("userName", metaData.getUserName());
        return info;
    }

    /**
     * 标准表获取方法
     */
    protected List<TableInfo> getTablesStandard(DatabaseMetaData metaData,
                                                String catalog, String schema,
                                                String[] tableTypes) throws SQLException {
        List<TableInfo> tables = new ArrayList<>();

        log.debug("使用标准方法获取表信息: catalog={}, schema={}, tableTypes={}",
                catalog, schema, Arrays.toString(tableTypes));

        try (ResultSet rs = metaData.getTables(catalog, schema, "%", tableTypes)) {
            while (rs.next()) {
                TableInfo table = createTableInfoFromResultSet(rs);
                if (table != null) {
                    tables.add(table);
                }
            }
        }

        log.info("标准方法获取到 {} 张表", tables.size());
        return tables;
    }

    /**
     * 带重试的表获取方法（备用方案）
     */
    protected List<TableInfo> getTablesWithRetry(DatabaseMetaData metaData,
                                                 String catalog, String schema,
                                                 String[] tableTypes) throws SQLException {
        // 尝试不同的参数组合
        String[][] parameterCombinations = getParameterCombinations(catalog, schema);

        for (String[] params : parameterCombinations) {
            try {
                List<TableInfo> tables = getTablesStandard(metaData, params[0], params[1], tableTypes);
                if (!tables.isEmpty()) {
                    log.info("使用参数 catalog={}, schema={} 成功获取 {} 张表",
                            params[0], params[1], tables.size());
                    return tables;
                }
            } catch (SQLException e) {
                log.debug("参数组合 catalog={}, schema={} 失败: {}", params[0], params[1], e.getMessage());
            }
        }

        return new ArrayList<>();
    }

    /**
     * 获取参数组合（可由子类重写）
     */
    protected String[][] getParameterCombinations(String catalog, String schema) {
        return new String[][]{
                {catalog, schema},    // 原始参数
                {null, null},         // 无限制
                {catalog, null},      // 仅catalog
                {null, schema},       // 仅schema
                {"%", "%"}           // 通配符
        };
    }

    /**
     * 丰富表信息（列、主键、索引）
     */
    protected void enrichTablesInfo(DatabaseMetaData metaData, String catalog,
                                    String schema, List<TableInfo> tables) throws SQLException {
        for (TableInfo table : tables) {
            try {
                table.setColumns(getColumnsSafe(metaData, catalog, schema, table.getName()));
                table.setPrimaryKeys(getPrimaryKeysSafe(metaData, catalog, schema, table.getName()));
                table.setIndexes(getIndexesSafe(metaData, catalog, schema, table.getName()));
            } catch (SQLException e) {
                log.warn("获取表 {} 的详细信息失败: {}", table.getName(), e.getMessage());
            }
        }
    }

    /**
     * 安全获取列信息
     */
    protected List<ColumnInfo> getColumnsSafe(DatabaseMetaData metaData,
                                              String catalog, String schema,
                                              String tableName) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();

        try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, "%")) {
            while (rs.next()) {
                ColumnInfo column = createColumnInfoFromResultSet(rs);
                if (column != null) {
                    columns.add(column);
                }
            }
        }

        return columns;
    }

    /**
     * 安全获取主键信息
     */
    protected List<PrimaryKeyInfo> getPrimaryKeysSafe(DatabaseMetaData metaData,
                                                      String catalog, String schema,
                                                      String tableName) throws SQLException {
        List<PrimaryKeyInfo> primaryKeys = new ArrayList<>();

        try (ResultSet rs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
            while (rs.next()) {
                PrimaryKeyInfo pk = createPrimaryKeyInfoFromResultSet(rs);
                if (pk != null) {
                    primaryKeys.add(pk);
                }
            }
        }

        return primaryKeys;
    }

    /**
     * 安全获取索引信息
     */
    protected List<IndexInfo> getIndexesSafe(DatabaseMetaData metaData,
                                             String catalog, String schema,
                                             String tableName) throws SQLException {
        List<IndexInfo> indexes = new ArrayList<>();

        try (ResultSet rs = metaData.getIndexInfo(catalog, schema, tableName, false, true)) {
            while (rs.next()) {
                IndexInfo index = createIndexInfoFromResultSet(rs);
                if (index != null) {
                    indexes.add(index);
                }
            }
        }

        return indexes;
    }

    // ========== 抽象方法（由子类实现） ==========

    /**
     * 获取catalog
     */
    protected abstract String getCatalog(Connection connection) throws Exception;

    /**
     * 获取schema
     */
    protected abstract String getSchema(Connection connection) throws Exception;

    /**
     * 获取表类型
     */
    protected abstract String[] getTableTypes();

    /**
     * 备用方案：当标准方法失败时调用
     */
    protected abstract List<TableInfo> getTablesWithFallback(Connection connection,
                                                             String catalog, String schema) throws Exception;

    // ========== 具体方法实现 ==========

    /**
     * 从ResultSet创建TableInfo
     */
    protected TableInfo createTableInfoFromResultSet(ResultSet rs) throws SQLException {
        String tableName = rs.getString("TABLE_NAME");
        if (tableName == null) return null;

        String tableType = rs.getString("TABLE_TYPE");
        String remarks = rs.getString("REMARKS");

        return new TableInfo(tableName, tableType, remarks);
    }

    /**
     * 从ResultSet创建ColumnInfo
     */
    protected ColumnInfo createColumnInfoFromResultSet(ResultSet rs) throws SQLException {
        String columnName = rs.getString("COLUMN_NAME");
        if (columnName == null) return null;

        String dataType = rs.getString("TYPE_NAME");
        int columnSize = rs.getInt("COLUMN_SIZE");
        int decimalDigits = rs.getInt("DECIMAL_DIGITS");

        String nullableStr = rs.getString("IS_NULLABLE");
        boolean isNullable = "YES".equalsIgnoreCase(nullableStr) || "1".equals(nullableStr);

        String defaultValue = rs.getString("COLUMN_DEF");
        String remarks = rs.getString("REMARKS");

        return new ColumnInfo(columnName, dataType, columnSize, decimalDigits,
                isNullable, defaultValue, remarks);
    }

    /**
     * 从ResultSet创建PrimaryKeyInfo
     */
    protected PrimaryKeyInfo createPrimaryKeyInfoFromResultSet(ResultSet rs) throws SQLException {
        String columnName = rs.getString("COLUMN_NAME");
        if (columnName == null) return null;

        String pkName = rs.getString("PK_NAME");
        short keySeq = rs.getShort("KEY_SEQ");

        return new PrimaryKeyInfo(columnName, pkName, keySeq);
    }

    /**
     * 从ResultSet创建IndexInfo
     */
    protected IndexInfo createIndexInfoFromResultSet(ResultSet rs) throws SQLException {
        String indexName = rs.getString("INDEX_NAME");
        if (indexName == null) return null;

        String columnName = rs.getString("COLUMN_NAME");
        boolean nonUnique = rs.getBoolean("NON_UNIQUE");
        short type = rs.getShort("TYPE");
        short ordinalPosition = rs.getShort("ORDINAL_POSITION");

        return new IndexInfo(indexName, columnName, nonUnique, type, ordinalPosition);
    }

    @Override
    public String getDatabaseSchemaAsText(Connection connection) throws Exception {
        Map<String, Object> metadata = getAllTablesMetadata(connection);
        return buildSchemaText(metadata, false);
    }

    @Override
    public String getSimplifiedDatabaseSchema(Connection connection) throws Exception {
        Map<String, Object> metadata = getAllTablesMetadata(connection);
        return buildSchemaText(metadata, true);
    }

    /**
     * 构建数据库结构文本描述
     */
    private String buildSchemaText(Map<String, Object> metadata, boolean simplified) {
        if (!Boolean.TRUE.equals(metadata.get("success"))) {
            return "无法获取数据库结构: " + metadata.get("error");
        }

        StringBuilder schemaText = new StringBuilder();

        @SuppressWarnings("unchecked")
        Map<String, Object> dbInfo = (Map<String, Object>) metadata.get("databaseInfo");
        if (dbInfo != null) {
            schemaText.append("数据库类型: ").append(dbInfo.get("databaseProductName")).append("\n");
        }

        @SuppressWarnings("unchecked")
        List<TableInfo> tables = (List<TableInfo>) metadata.get("tables");

        if (tables != null && !tables.isEmpty()) {
            if (simplified) {
                appendSimplifiedSchema(schemaText, tables);
            } else {
                appendDetailedSchema(schemaText, tables);
            }
        } else {
            schemaText.append("数据库中没有找到表\n");
        }

        return schemaText.toString();
    }

    private void appendDetailedSchema(StringBuilder schemaText, List<TableInfo> tables) {
        schemaText.append("数据库表结构：\n\n");
        for (TableInfo table : tables) {
            schemaText.append("表名: ").append(table.getName());
            if ("VIEW".equalsIgnoreCase(table.getType())) {
                schemaText.append(" (视图)");
            }
            schemaText.append("\n");

            if (table.getRemarks() != null && !table.getRemarks().isEmpty()) {
                schemaText.append("描述: ").append(table.getRemarks()).append("\n");
            }

            schemaText.append("列信息：\n");
            for (ColumnInfo column : table.getColumns()) {
                schemaText.append("  - ").append(column.getName())
                        .append(" ").append(column.getDataType());

                if (column.getColumnSize() > 0) {
                    schemaText.append("(").append(column.getColumnSize());
                    if (column.getDecimalDigits() > 0) {
                        schemaText.append(",").append(column.getDecimalDigits());
                    }
                    schemaText.append(")");
                }

                if (!column.isNullable()) {
                    schemaText.append(" NOT NULL");
                }
                if (column.getDefaultValue() != null) {
                    schemaText.append(" DEFAULT ").append(column.getDefaultValue());
                }
                if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
                    schemaText.append(" COMMENT '").append(column.getRemarks()).append("'");
                }
                schemaText.append("\n");
            }

            if (!table.getPrimaryKeys().isEmpty()) {
                schemaText.append("主键: ");
                for (PrimaryKeyInfo pk : table.getPrimaryKeys()) {
                    schemaText.append(pk.getColumnName()).append(" ");
                }
                schemaText.append("\n");
            }

            schemaText.append("\n");
        }
    }

    private void appendSimplifiedSchema(StringBuilder schemaText, List<TableInfo> tables) {
        schemaText.append("表结构：\n");
        for (TableInfo table : tables) {
            schemaText.append("- ").append(table.getName()).append(": ");
            for (int i = 0; i < table.getColumns().size(); i++) {
                ColumnInfo column = table.getColumns().get(i);
                schemaText.append(column.getName()).append(" ").append(column.getDataType());
                if (i < table.getColumns().size() - 1) {
                    schemaText.append(", ");
                }
            }
            schemaText.append("\n");
        }
    }
}